package edu.jit.flowers.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import edu.jit.flowers.bean.Users;
import edu.jit.flowers.utils.JDBCUtil;

/*
 * 用户的增删改查
 * 
 * */
public class UsersDAO {
	// 登录验证
	public Users loginCheck(String username, String password) {
		String sql = "select u_name as uname, u_pass as upassword " + "from users " + "where "
				+ "u_name = ? and u_pass = ?";
		Users u = null;
		QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
		try {
			u = runner.query(sql, new BeanHandler<>(Users.class), username, password);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return u;
	}

	// 添加用户
	public int addUsers(Users u) {
		int rows = 0;
		String sql = "insert into users values(0,?,?,?,?,?,?,?,?)";

		QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
		try {
			rows = runner.update(sql, u.getuName(), u.getuRealName(), u.getuAge(), u.getuTel(), u.getuGender(),
					u.getuPass(), u.getuRole(), u.getuAddress());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rows;

	}

	// 删除用户
	public int deleteUsersById(Users u) {
		int rows = 0;
		String sql = "delete from users where u_id = ?";
		QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
		try {
			rows = runner.update(sql, u.getuId());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rows;
	}

	// 修改用户角色
	public int updateRoleUsersById(Users u) {
		int rows = 0;
		String sql = "update users set u_role=? where u_id=?";
		QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
		try {
			rows = runner.update(sql, u.getuRole(), u.getuId());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rows;

	}

	// 修改用户
	public int updateUsersByUName(Users u) {
		int rows = 0;
		String sql = "update users set u_real_name= ?,u_age= ?, "
				+ "u_tel= ?,u_gender= ?,u_role = ?,u_address= ? where u_name=?";
		QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
		try {
			rows = runner.update(sql, u.getuRealName(), u.getuAge(), u.getuTel(), u.getuGender(),
					u.getuRole(),u.getuAddress(), u.getuName());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rows;

	}

	// 查询所有用户
	public List<Users> selectAll(Users u) {
		List<Users> users = new ArrayList<>();
		String sql = "select * from users";
		QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
		try {
			users = runner.execute(sql, new BeanHandler<>(Users.class), u);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return users;
	}

	// 根据用户名查询用户
	public Users selectAllCommonByUName(String username) {
		String sql = "select u_id as uid,u_name as uname, u_real_name as urealname,u_age as uage, "
				+ "u_tel as utel,u_gender as ugender,u_address as uaddress,u_pass as upass, " + "u_role as urole "
				+ "from users " + "where " + "u_name = ? and u_role = 0 ";
		Users u = null;
		QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
		try {
			u = runner.query(sql, new BeanHandler<>(Users.class), username);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return u;
	}

	// 根据用户角色查询所有管理员用户
	public List<Users> selectAllManageByRole() {
		List<Users> users = new ArrayList<>();
		String sql = "select u_name as uname,u_real_name as urealname,u_age as uage,"
				+ "u_tel as utel,u_gender as ugender,u_address as uaddress,u_pass as upass,"
				+ "u_role as urole from users where u_role=1";
		QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
		try {
			users = runner.query(sql, new BeanListHandler<>(Users.class));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return users;
	}

	// 根据用户角色查询所有普通用户
	public List<Users> selectAllCommonByRole() {
		List<Users> users = new ArrayList<>();
		String sql = "select u_name as uname,u_real_name as urealname,u_age as uage,"
				+ "u_tel as utel,u_gender as ugender,u_address as uaddress,u_pass as upass,"
				+ "u_role as urole from users where u_role=0";
		QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
		try {
			users = runner.query(sql, new BeanListHandler<>(Users.class));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return users;
	}

}
